/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import entity.Car;
import entity.City;
import entity.Station;
import interf.ICar;
import interf.ICity;
import interf.IStation;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author I
 */
public class DAL {
    Connection con;
    PreparedStatement stmt;
    ResultSet rs;
    public DAL()
    {
        try{
         Class.forName("org.postgresql.Driver");
         }catch (ClassNotFoundException ex) {
           
        }
    }
    public List<ICity> getListCity(){
        List<ICity> listCity = new ArrayList<ICity>();
        try{
            con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/RentCar", "postgres", "qwe123");
            stmt = con.prepareStatement("SELECT id, name, region FROM city");
            rs = stmt.executeQuery();
            while(rs.next())
            {
                listCity.add(new City(rs.getString("region"), rs.getString("name"), rs.getLong("id")));
            }
        } catch (SQLException ex) {
            String str = ex.getMessage();
            System.out.println(ex.getMessage());
        }
        finally{
             if(con!=null){
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DAL.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
                }
             }
        }
        return listCity;
    }
    
    public List<IStation> getListStation(){
         List<IStation> listStation = new ArrayList<IStation>();
        try{
            con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/RentCar", "postgres", "qwe123");
            stmt = con.prepareStatement("SELECT station.id, number, address, city.name, city.region, station.cityid FROM station LEFT JOIN city ON city.id = station.cityid");
            rs = stmt.executeQuery();
            while(rs.next())
            {
                listStation.add(new Station(rs.getString("address"), rs.getString("number"), rs.getString("region"), rs.getString("name"), rs.getLong(1)));
            }
        } catch (SQLException ex) {
            String str = ex.getMessage();
            System.out.println(ex.getMessage());
        }
        finally{
             if(con!=null){
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DAL.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
                }
             }
        }
        return listStation;
    }
    
    public List<IStation> getListStation(long idCity){
         List<IStation> listStation = new ArrayList<IStation>();
        try{
            con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/RentCar", "postgres", "qwe123");
            stmt = con.prepareStatement("SELECT station.id, number, address, city.name, city.region, station.cityid FROM station LEFT JOIN city ON city.id = station.cityid WHERE station.cityid = ?");
            stmt.setLong(1, idCity);
            rs = stmt.executeQuery();
            while(rs.next())
            {
                listStation.add(new Station(rs.getString("address"), rs.getString("number"), rs.getString("region"), rs.getString("name"), rs.getLong(1)));
            }
        } catch (SQLException ex) {
            String str = ex.getMessage();
            System.out.println(ex.getMessage());
        }
        finally{
             if(con!=null){
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DAL.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
                }
             }
        }
        return listStation;
    }
    
    public List<ICar> getListCar(){
        List<ICar> listCar = new ArrayList<ICar>();
        try{
            con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/RentCar", "postgres", "qwe123");
            stmt = con.prepareStatement("SELECT car.id, car.mark, car.number, car.typebody, car.price, car.transmission, car.luggage, car.quantityofplaces, car.conditioner, car.classofcar, car.numberofstation, station.number, class_of_car.name FROM car LEFT JOIN station ON station.id = car.numberofstation LEFT JOIN class_of_car ON class_of_car.id = car.classofcar");
            rs = stmt.executeQuery();
            while(rs.next())
            {
                ICar car = new Car();
                car.setID(rs.getLong("id"));
                car.setMark(rs.getString("mark"));
                car.setNumber(rs.getString(3));
                car.setTypeBody(rs.getString("typebody"));
                car.setPrice(rs.getFloat("price"));
                car.setTransmission(rs.getString("transmission"));
                car.setLuggage((short)rs.getInt("luggage"));
                car.setQuantityOfPlaces((short)rs.getInt("quantityofplaces"));
                car.setConditioner(rs.getBoolean("conditioner"));
                car.setClassOfCar(rs.getString(13));
                car.setNumberOfStation(rs.getString(12));
                listCar.add(car);
            }
        } catch (SQLException ex) {
            String str = ex.getMessage();
            System.out.println(ex.getMessage());
        }
        finally{
             if(con!=null){
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DAL.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
                }
             }
        }
        return listCar;
    }
    
    public List<ICar> getListCar(long idStation){
         List<ICar> listCar = new ArrayList<ICar>();
        try{
            con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/RentCar", "postgres", "qwe123");
            stmt = con.prepareStatement("SELECT car.id, car.mark, car.number, car.typebody, car.price, car.transmission, car.luggage, car.quantityofplaces, car.conditioner, car.classofcar, car.numberofstation, station.number, class_of_car.name FROM car LEFT JOIN station ON station.id = car.numberofstation LEFT JOIN class_of_car ON class_of_car.id = car.classofcar WHERE car.numberofstation = ?");
            stmt.setLong(1, idStation);
            rs = stmt.executeQuery();
            while(rs.next())
            {
                ICar car = new Car();
                car.setID(rs.getLong("id"));
                car.setMark(rs.getString("mark"));
                car.setNumber(rs.getString(3));
                car.setTypeBody(rs.getString("typebody"));
                car.setPrice(rs.getFloat("price"));
                car.setTransmission(rs.getString("transmission"));
                car.setLuggage((short)rs.getInt("luggage"));
                car.setQuantityOfPlaces((short)rs.getInt("quantityofplaces"));
                car.setConditioner(rs.getBoolean("conditioner"));
                car.setClassOfCar(rs.getString(13));
                car.setNumberOfStation(rs.getString(12));
                listCar.add(car);
            }
        } catch (SQLException ex) {
            String str = ex.getMessage();
            System.out.println(ex.getMessage());
        }
        finally{
             if(con!=null){
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DAL.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
                }
             }
        }
        return listCar;
    }
}
